package net.zjitc.dao.Impl;

import net.zjitc.dao.ProductDao;
import net.zjitc.pojo.CategorySecond;
import net.zjitc.pojo.Evaluate;
import net.zjitc.pojo.Product;
import net.zjitc.pojo.User;
import net.zjitc.utils.PageBean;
import net.zjitc.utils.db.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/11/22.
 */
public class ProductDaoImpl implements ProductDao{
    QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
    public PageBean<Product> findPageBean(int page, int pageSize) {
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);

        //查总条数
        String sql="select count(*) from product";
        //数据
        String sqll="select * from product order by pid limit ?,?";

        try {

            Number number= (Number) runner.query(sql,new ScalarHandler());
            int count=number.intValue();
            pageBean.setCount(count);

            List<Product> list= (List<Product>) runner.query(sqll,new BeanListHandler(Product.class),(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void productAdd(Product product) {
        String sql="insert into product (pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) values (?,?,?,?,?,?,?,?)";
        try {
            runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().csid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Product findProductById(String pid) {
        String sql="select * from product where pid=?";
        try {
            Map<String,Object> map=runner.query(sql,new MapHandler(),pid);
            CategorySecond categorySecond=new CategorySecond();
            Product product=new Product();

            BeanUtils.populate(product,map);
            BeanUtils.populate(categorySecond,map);

            product.setCategorySecond(categorySecond);

            return product;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void productEdit(Product product) {
        String sql="update product set pname=?,market_price=?,shop_price=?,image=?,pdesc=?,is_hot=?,pdate=?,csid=? where pid=?";

        try {
            runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().csid,product.getPid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void productDelete(String pid) {
        String sql="delete from product where pid=?";
        try {
            runner.update(sql,pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Product> findAllProduct() {
        String sql="select * from product order by pdate";
        try {
            List<Product>list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PageBean<Product> findPageBeanByCid(int page, int pageSize, int cid) {
        PageBean<Product> bean=new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);

        String sqlCount="select COUNT(*) from product p join categorysecond cs,category c where p.csid=cs.csid AND cs.cid=c.cid AND c.cid=?";
        String sql="select p.* from product p join categorysecond cs,category c where p.csid=cs.csid AND cs.cid=c.cid AND c.cid=? limit ?,?";

        try {
            //查询有多少条数据
            Number num= (Number) runner.query(sqlCount,new ScalarHandler(),cid);
            bean.setCount(num.intValue());

            //查询数据
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class),cid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PageBean<Product> findPageBeanByCsid(int page, int pageSize, int csid) {
        PageBean<Product> bean=new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);

        String sqlCount="select Count(*) from product p join categorysecond cs,category c where p.csid=cs.csid AND cs.cid=c.cid AND cs.csid=?";
        String sql="select p.* from product p join categorysecond cs,category c where p.csid=cs.csid AND cs.cid=c.cid AND cs.csid=? limit ?,?";

        try {
            Number Num= (Number) runner.query(sqlCount,new ScalarHandler(),csid);
            bean.setCount(Num.intValue());

            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class),csid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Evaluate> findEvaluateByPid(String pid) {
        String sql="select * from evaluate e join product p,user u where e.pid=p.pid and e.uid=u.uid and p.pid=?";

        List<Evaluate> evaluateList=new ArrayList<Evaluate>();
        List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
        try {
            list=runner.query(sql,new MapListHandler(),pid);
            for(Map<String,Object> m:list)
            {
                User user=new User();
                Product product=new Product();
                Evaluate evaluate=new Evaluate();

                BeanUtils.populate(user,m);
                BeanUtils.populate(product,m);
                BeanUtils.populate(evaluate,m);

                evaluate.setUser(user);
                evaluate.setProduct(product);
                evaluateList.add(evaluate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return evaluateList;
    }


}
